/*
 * Copyright (c) 2015. For Intelligent Group.
 */

package com.intelligent.ispc.core.service.impl;

import com.csvreader.CsvReader;
import com.google.common.collect.HashBasedTable;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
import com.google.common.collect.Table;
import com.intelligent.ispc.aop.ApplicationModule;
import com.intelligent.ispc.cache.ReportCacheFactory;
import com.intelligent.ispc.cache.TestDataCacheFactory;
import com.intelligent.ispc.common.mapper.JsonMapper;
import com.intelligent.ispc.common.utils.*;
import com.intelligent.ispc.common.web.ProcessEvent;
import com.intelligent.ispc.core.dto.*;
import com.intelligent.ispc.core.entity.*;
import com.intelligent.ispc.core.repository.jpa.ProjectDao;
import com.intelligent.ispc.core.repository.jpa.TestFileDao;
import com.intelligent.ispc.core.repository.mongo.GRRTestDataDao;
import com.intelligent.ispc.core.service.*;
import com.intelligent.ispc.utils.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.task.TaskExecutor;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.data.domain.Sort;
import org.springframework.data.mongodb.core.IndexOperations;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.index.Index;
import org.springframework.data.mongodb.core.index.IndexInfo;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.stereotype.Service;

import java.io.File;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

/**
 * Created by Julia.Zhou on 2016/06/03.
 */
@Service
public class GRRDataIOServiceImpl implements GRRDataIOService {

    private Logger logger = LoggerFactory.getLogger(GRRDataIOServiceImpl.class);

    @Autowired
    private MongoTemplate mongoTemplate;

    @Autowired
    private ProjectDao projectDao;

    @Autowired
    private TestFileDao testFileDao;

    @Autowired
    private GRRTestDataDao grrTestDataDao;

    @Autowired
    private SecurityService securityService;

    @Autowired
    private SystemConfig systemConfig;

    @Autowired
    private TestDataCacheFactory testDataCacheFactory;

    @Autowired
    private ReportCacheFactory reportCacheFactory;

    private JsonMapper mapper = JsonMapper.nonDefaultMapper();

    @Autowired
    private GRRTestDataService grrTestDataService;

    @Autowired
    private ProjectService projectService;

    @Autowired
    private TestFileService testFileService;

    @Autowired
    private TaskExecutor taskExecutor;

    @Autowired
    private LogService logService;

    @Override
    public void importGRRDataFromCvs(GRRDataImportParamDto grrDataImportParamDto, ProcessEvent processEvent) {
        String userName = securityService.getCurrentUserLoginName();
        String projectId = grrDataImportParamDto.getProjectId();
        String filePath = grrDataImportParamDto.getFilePath();
        long fileSize = grrDataImportParamDto.getFileSize();
        taskExecutor.execute(new Thread() {
            public void run() {
                CsvReader cr = null;
                GRRTestData testData = null;
                Project project = null;
                List<GRRTestData> lastTestDataList = Lists.newArrayList();
                List<PSAttributeDto> psAttributeDtoList = Lists.newArrayList();
                List<String> colNames = Lists.newArrayList();
                Map<String, List<Long>> errorMessage = new HashMap<>();
                Long failTotal = 0L;
                Long successTotal = 0L;
                Long total = 0L;
                String testFileUuid = Identities.uuid();
                double process = 50;
                boolean isFail = false;
                boolean isHasFile = false;
                Map<Double, Double> mapProcess = new HashMap<Double, Double>();
                Map<String, Boolean> itemNameMap = new HashMap<String, Boolean>();
                Map<String, List<String>> stationMap = new HashMap<String, List<String>>();
                Date grrStartTime = null;
                Date grrEndTime = null;
                boolean isLastUpdate = true;
                String testFileId = null;

                long fileSizeOffset = 0;
                try {
                    project = getProject(projectId);
                    psAttributeDtoList = getPSAttributeDtos(projectId);
                    logger.debug("Reading csv file: {}", filePath);
                    errorMessage.put("failTotal", Lists.newArrayList());
                    cr = new CsvReader(filePath);
                    cr.readHeaders();

                    Table<Long, Integer, String> itemLimitTable = HashBasedTable.create();
                    Table<Long, String, String> dataRowTable = HashBasedTable.create();
                    List<String> errorColNames = Lists.newArrayList();
                    while (cr.readRecord()) {
                        long currentRowNum = cr.getCurrentRecord();
                        String[] row = cr.getValues();

                        //get the limit param (upper/lower...)
                        if (currentRowNum < 4) {
                            long rowByteSize = 0;
                            for (int i = 0; i < row.length; i++) {
                                if (StringUtils.isBlank(cr.get(i))) {
                                    continue;
                                }
                                itemLimitTable.put(currentRowNum, i, cr.get(i));
                                rowByteSize += cr.get(i).getBytes().length;

                                if (currentRowNum == 0 && StringUtils.isNotBlank(cr.get(i))) {
                                    colNames.add(cr.get(i));
                                }
                            }
                            fileSizeOffset += rowByteSize;
                            if (colNames == null || colNames.isEmpty()) {
                                processEvent.update(process, "The column names of second row can not be empty.", fileSizeOffset, isLastUpdate);
                                isFail = true;
                                break;
                            }
                            if (currentRowNum == 0) {
                                for (PSAttributeDto psAttributeDto : psAttributeDtoList) {
                                    if (!colNames.contains(psAttributeDto.getValue())) {
                                        errorColNames.add(psAttributeDto.getValue());
                                    }
                                }

                                if (errorColNames != null && !errorColNames.isEmpty()) {
                                    if (errorColNames.size() == 1) {
                                        processEvent.update(process, "The data without the attributes [" + errorColNames.get(0) + "].\\\\n", fileSizeOffset, isLastUpdate);
                                    } else {
                                        String errorColName = StringUtils.join(errorColNames, ", ");
                                        processEvent.update(process, "The data without the attributes [" + errorColName + "].\\\\n", fileSizeOffset, isLastUpdate);
                                    }
                                    isFail = true;
                                    break;
                                }
                            }
                            process += (double) rowByteSize / (double) fileSize * 50;
                        } else {
                            ++total;
                            //real test data
                            dataRowTable.row(0L).clear();

                            long rowByteSize = 0;
                            for (int i = 0; i < row.length; i++) {
                                rowByteSize += cr.get(i).getBytes().length;
                                if (StringUtils.isBlank(itemLimitTable.row(0L).get(i))) {
                                    continue;
                                }
                                dataRowTable.put(0L, itemLimitTable.row(0L).get(i), cr.get(i));
                            }
                            fileSizeOffset += rowByteSize;
                            process += (double) rowByteSize / (double) fileSize * 50;
                            testData = importRowData(itemLimitTable, dataRowTable, project, grrDataImportParamDto, colNames, psAttributeDtoList, errorMessage, itemNameMap, stationMap, currentRowNum);
                            if (testData != null && !testData.getTestData().isEmpty()) {
                                if (!isFail && !isHasFile) {
                                    testFileId = createTestFile(filePath, project, testFileUuid, userName, grrDataImportParamDto.getGrrName()).toString();
                                    isHasFile = true;
                                }

                                if (grrStartTime == null) {
                                    grrStartTime = (Date) testData.getTestData().get(TestData.PSA_START_TIME);
                                } else {
                                    if (grrStartTime.compareTo((Date) testData.getTestData().get(TestData.PSA_START_TIME)) == 1) {
                                        grrStartTime = (Date) testData.getTestData().get(TestData.PSA_START_TIME);
                                    }
                                }

                                if (grrEndTime == null) {
                                    grrEndTime = (Date) testData.getTestData().get(TestData.PSA_START_TIME);
                                } else {
                                    if (((Date) testData.getTestData().get(TestData.PSA_START_TIME)).compareTo(grrEndTime) == 1) {
                                        grrEndTime = (Date) testData.getTestData().get(TestData.PSA_START_TIME);
                                    }
                                }
                                lastTestDataList.add(testData);
                            }
                            if (rowByteSize == 0) {
                                continue;
                            }

                            if (!mapProcess.containsKey(Math.floor(process))) {
                                processEvent.update(process, "", fileSizeOffset, isLastUpdate);
                                mapProcess.put(Math.floor(process), process);
                            }
                        }
                    }
                    if (!isFail) {
                        Map<String, Object> searchParams = new HashMap<String, Object>();
                        searchParams.put(Constant.GLOBAL_PROJECT_ID, projectId);
                        searchParams.put(Constant.GRR_OPERATOR_VALUE, grrDataImportParamDto.getOperatorValue());
                        searchParams.put(Constant.GRR_PART_VALUE, grrDataImportParamDto.getPartValue());
                        searchParams.put(Constant.GRR_TRIAL_VALUE, grrDataImportParamDto.getTrialValue());
                        //searchParams.put(Constant.GRR_TEST_STRATEGY, grrDataImportParamDto.getTestStrategy());
                        searchParams.put(Constant.GRR_START_TIME, grrStartTime);
                        searchParams.put(Constant.GRR_END_TIME, grrEndTime);

                        BaseDto baseDto = new BaseDto();
                        baseDto.setKey(grrDataImportParamDto.getAnalysisKeysDto().getPartKey());
                        baseDto.setValue(grrDataImportParamDto.getAnalysisKeysDto().isPartKeyFlag());
                        searchParams.put(Constant.GRR_PART_KEY, baseDto);
                        baseDto = new BaseDto();
                        baseDto.setKey(grrDataImportParamDto.getAnalysisKeysDto().getGroup1Key());
                        baseDto.setValue(grrDataImportParamDto.getAnalysisKeysDto().isGroup1KeyFlag());
                        searchParams.put(Constant.GRR_GROUP1_KEY, baseDto);
                        baseDto = new BaseDto();
                        baseDto.setKey(grrDataImportParamDto.getAnalysisKeysDto().getGroup2Key());
                        baseDto.setValue(grrDataImportParamDto.getAnalysisKeysDto().isGroup2KeyFlag());
                        searchParams.put(Constant.GRR_GROUP2_KEY, baseDto);
                        baseDto = new BaseDto();
                        baseDto.setKey(grrDataImportParamDto.getAnalysisKeysDto().getDimensionKey());
                        baseDto.setValue(grrDataImportParamDto.getAnalysisKeysDto().isDimensionKeyFlag());
                        searchParams.put(Constant.GRR_DIMENSION_KEY, baseDto);
                        searchParams.put(Constant.GRR_DIMENSION_VALUE, grrDataImportParamDto.getAnalysisKeysDto().getDimensionValue());

                        grrTestDataService.checkGRRTestData(searchParams, lastTestDataList);
                        mongoTemplate.insert(lastTestDataList, Constant.MONGODB_GRR_COLLECTION + projectId);
                        successTotal += Long.valueOf(lastTestDataList.size());
                    }

                    cr.close();
                    clearSpcAnalyseCache(projectId);
                } catch (ApplicationException ex) {
                    isFail = true;
                  /*  if (ex.getMessage() != null && ex.getMessage().equals(ExceptionMessages.EXCEPTION_GRR_DATA_COUNT_ERROR)) {
                        processEvent.update(process, "\\\\nFailed to upload the data.\\\\nFailure cause:\\\\nData Count Error.", fileSizeOffset, isLastUpdate);
                    } else if (ex.getMessage() != null && ex.getMessage().equals(ExceptionMessages.EXCEPTION_GRR_DATA_STATION_ERROR)) {
                        processEvent.update(process, "\\\\nFailed to upload the data.\\\\nFailure cause:\\\\nStation error.", fileSizeOffset, isLastUpdate);
                    }*/

                    if (ex.getMessage() != null && ex.getMessage().contains(ExceptionMessages.EXCEPTION_GRR_DATA_SN_COUNT_ERROR)) {
                        String error = ex.getMessage().substring(ex.getMessage().lastIndexOf("R") + 1, ex.getMessage().length());
                        processEvent.update(process, "\\\\nFailed to upload the data.\\\\nFailure cause:\\\\nLack of ["+ error+"] Part.", fileSizeOffset, isLastUpdate);
                    } else if (ex.getMessage() != null && ex.getMessage().contains(ExceptionMessages.EXCEPTION_GRR_DATA_GROUP_COUNT_ERROR)) {
                        String error = ex.getMessage().substring(ex.getMessage().lastIndexOf("R") + 1, ex.getMessage().length());
                        processEvent.update(process, "\\\\nFailed to upload the data.\\\\nFailure cause:\\\\nLack of [" +error+ "] Group.", fileSizeOffset, isLastUpdate);
                    } else if (ex.getMessage() != null && ex.getMessage().contains(ExceptionMessages.EXCEPTION_GRR_DATA_SN_TYPE_ERROR)) {
                        String error = ex.getMessage().substring(ex.getMessage().lastIndexOf("["), ex.getMessage().length());
                        processEvent.update(process, "\\\\nFailed to upload the data.\\\\nFailure cause:\\\\n" + error + " Number Error.", fileSizeOffset, isLastUpdate);
                    } else if (ex.getMessage() != null && ex.getMessage().contains(ExceptionMessages.EXCEPTION_GRR_DATA_DIMENSION_INCOMPLETE)) {
                        String error = ex.getMessage().substring(ex.getMessage().lastIndexOf("["), ex.getMessage().length());
                        processEvent.update(process, "\\\\nFailed to upload the data.\\\\nFailure cause:\\\\nLack of "+ error + " Operator.", fileSizeOffset, isLastUpdate);
                    } else {
                        processEvent.update(process, "\\\\nFailed to upload the data.\\\\nFailure cause:\\\\nSystem exception, please contact your administrator.", fileSizeOffset, isLastUpdate);
                    }

                    throw new ApplicationException(ex.getMessage());
                } catch (DataAccessResourceFailureException e) {
                    isFail = true;
                    processEvent.update(process, "\\\\nFailed to upload the data.\\\\nFailure cause:\\\\nMongodb connection exception, please contact your administrator.", fileSizeOffset, isLastUpdate);
                    throw new ApplicationException(e.getMessage());
                } catch (Exception e) {
                    isFail = true;
                    processEvent.update(process, "\\\\nFailed to upload the data.\\\\nFailure cause:\\\\nSystem exception, please contact your administrator.", fileSizeOffset, isLastUpdate);
                    throw new ApplicationException(e.getMessage());
                } finally {
                    if (cr != null) {
                        cr.close();
                    }

                    if (!isFail) {
                        createIndexForTestData(projectId);
                        setStations(projectId, stationMap);
                        processEvent.update(100, "Successfully saved data: <strong>" + successTotal + "</strong>\\\\nTotal data: <strong>" + total + "</strong>\\\\nUpload the data done.\\\\n", fileSizeOffset, isLastUpdate);
                        if (errorMessage.get("failTotal") != null && !errorMessage.get("failTotal").isEmpty()) {
                            processEvent.update(100, "There are <strong>" + errorMessage.get("failTotal").size() + "</strong> data ignored because the data format problem.\\\\n" +
                                    "Exception lines are as follows: <strong>\\\\n" + errorMessage.get("failTotal") + "</strong>\\\\n", fileSizeOffset, isLastUpdate);
                        }
                        saveGRRItem(grrDataImportParamDto, itemNameMap);
                        testFileService.updateTestFileStatus(testFileUuid, TestFileStatus.DONE);
                        saveImportDataLog(project, userName, filePath, fileSize, LogUtil.SUCCESS.getCode());
                    } else {
                        if (StringUtils.isNotBlank(testFileId)) {
                            logger.debug("Delete test File: {}", testFileUuid);
                            TestFile testFile = testFileDao.findByTestFileUUID(testFileUuid);
                            if (testFile != null) {
                                String projectId = testFile.getProject().getId().toString();
                                String id = testFile.getId().toString();
                                logger.info("Delete test file: originalName={}, project={}, uuid={}, operator={}", testFile.getOriginalName(), project.getName(), testFileUuid, testFile.getCreateBy());
                                testFileDao.delete(testFile);
                            }
                        }
                        saveImportDataLog(project, userName, filePath, fileSize, LogUtil.FAILURE.getCode());
                    }
                    File uploadFile = new File(filePath);
                    if (uploadFile.exists()) {
                        uploadFile.delete();
                    }
                }
            }
        });
    }

    private void saveGRRItem(GRRDataImportParamDto grrDataImportParamDto, Map<String, Boolean> itemNameMap) {
        if (grrDataImportParamDto == null || StringUtils.isBlank(grrDataImportParamDto.getProjectId())
                || StringUtils.isBlank(grrDataImportParamDto.getGrrName())) {
            throw new ApplicationException(ExceptionMessages.EXCEPTION_PARAMETER_INPUT_INVALID);
        }
        Map<String, Object> searchParams = new HashMap<>();
        searchParams.put("projectId", grrDataImportParamDto.getProjectId());
        searchParams.put("grrName", grrDataImportParamDto.getGrrName());
        GRRTestData grrTestData = grrTestDataDao.findGRRTestData(searchParams);

        List<BaseDto> testItemNameList = Lists.newArrayList();
        if (grrTestData != null && grrTestData.getTestItems() != null && !grrTestData.getTestItems().isEmpty()) {
            for (TestItem testItem : grrTestData.getTestItems()) {
                String itemName = testItem.getName();
                if (itemNameMap.containsKey(itemName) && itemNameMap.get(itemName) != null && itemNameMap.get(itemName)) {
                    BaseDto baseDto = new BaseDto();
                    baseDto.setKey(itemName);
                    if (testItemNameList.size() <= systemConfig.getItemNameSize() - 1) {
                        baseDto.setValue(true);
                    } else {
                        baseDto.setValue(false);
                    }
                    testItemNameList.add(baseDto);
                }
            }
            ;
        }

        ProjectDto projectDto = new ProjectDto();
        projectDto.setId(Long.valueOf(grrDataImportParamDto.getProjectId()));
        GRRItemDto grrItemDto = new GRRItemDto();
        grrItemDto.setGrrName(grrDataImportParamDto.getGrrName());
        //grrItemDto.setTestStrategy(grrDataImportParamDto.getTestStrategy());
        grrItemDto.setPartValue(grrDataImportParamDto.getPartValue());
        grrItemDto.setOperatorValue(grrDataImportParamDto.getOperatorValue());
        grrItemDto.setTrialValue(grrDataImportParamDto.getTrialValue());
        grrItemDto.setCreateBy(grrDataImportParamDto.getCreateBy());
        grrItemDto.setProject(projectDto);
        grrItemDto.setDelete(false);
        grrItemDto.setTestItemNames(mapper.toJson(testItemNameList));

        grrItemDto.setAnalysisKeys(mapper.toJson(grrDataImportParamDto.getAnalysisKeysDto()));
        grrTestDataService.saveGRRItem(grrItemDto);
    }

    private Project getProject(String projectId) {
        if (projectId == null) {
            throw new ApplicationException(ExceptionMessages.EXCEPTION_PARAMETER_INPUT_INVALID);
        }

        logger.debug("Checking project: {}", projectId);

        Project project = projectDao.findById(Long.valueOf(projectId));
        if (project == null) {
            throw new ApplicationException(ExceptionMessages.EXCEPTION_OBJECT_DOES_NOT_EXIST);
        }

        return project;
    }

    private List<PSAttributeDto> getPSAttributeDtos(String projectId) {
        if (projectId == null) {
            throw new ApplicationException(ExceptionMessages.EXCEPTION_PARAMETER_INPUT_INVALID);
        }

        logger.debug("Checking project: {}", projectId);

        List<PSAttributeDto> psAttributeDtoList = projectService.findPSAttributeByProject(projectId);
        if (psAttributeDtoList == null || psAttributeDtoList.isEmpty()) {
            throw new ApplicationException(ExceptionMessages.EXCEPTION_OBJECT_DOES_NOT_EXIST);
        }

        return psAttributeDtoList;
    }

    private Long createTestFile(String filePath, Project project, String uuid, String userName, String grrName) {

        File file = new File(filePath);
        String fileName = file.getName();
        String originalName = fileName.substring(fileName.indexOf("_") + 1, fileName.length());
        TestFileDto testFile = new TestFileDto();
        testFile.setTestFileUUID(uuid);
        testFile.setImportTime(new Date());
        testFile.setOriginalName(originalName);
        ProjectDto dto = new ProjectDto();
        dto.setId(project.getId());
        testFile.setProject(dto);
        testFile.setStandardName(grrName);
        testFile.setSavePath(filePath);
        testFile.setDelete(false);
        testFile.setCreateBy(userName);
        testFile.setStatus(TestFileStatus.PROCESSING);
        testFile.setDataType(DataSourceType.GRR.getCode());
        return testFileService.saveTestFile(testFile);
    }

    private GRRTestData importRowData(Table columnTable, Table dataRowTable, Project project, GRRDataImportParamDto grrDataImportParamDto, List<String> colNames, List<PSAttributeDto> psAttributeDtoList, Map<String, List<Long>> errorMessage, Map<String, Boolean> itemNameMap, Map<String, List<String>> stationMap, long currentRowNum) {
        String grrName = grrDataImportParamDto.getGrrName();
        String partKey = grrDataImportParamDto.getAnalysisKeysDto().getPartKey();
        String dimensionKey = grrDataImportParamDto.getAnalysisKeysDto().getDimensionKey();

        Map<String, String> row = dataRowTable.row(0L);
        long rows = currentRowNum + 2;
        GRRTestData testData = new GRRTestData();
        Set<String> failItemSet = Sets.newHashSet();
        Map<String, Object> testDataMap = new HashMap<>();

        String stationId = null;
        String slotNo = null;
        List<DUTAttribute> dutAttributes = Lists.newArrayList();

        List<String> psAttributes = Lists.newArrayList();
        for (PSAttributeDto psAttributeDto : psAttributeDtoList) {
            String name = psAttributeDto.getName();
            String value = psAttributeDto.getValue();

            psAttributes.add(value);
            String cloValue = row.get(value);
            String realValue = StringUtils.isSpecialBlank(cloValue) ? null : cloValue;
            if (partKey.equals(name) || (StringUtils.isNotBlank(dimensionKey) && dimensionKey.equals(name))) {
                if (realValue == null) {
                    errorMessage.get("failTotal").add(rows);
                    return testData;
                }
                if (psAttributeDto.getIsDefault()) {
                    testDataMap.put(name, realValue);
                } else {
                    DUTAttribute dutAttribute = new DUTAttribute();
                    dutAttribute.setAttributeName(name);
                    dutAttribute.setAttributeValue(realValue);
                    dutAttributes.add(dutAttribute);
                }
            } else {
                if (psAttributeDto.getIsDefault()) {
                    if (TestData.PSA_SERIAL_NUMBER.equals(name)) {
                        testDataMap.put(name, realValue);
                    } else if (TestData.PSA_STATION_ID.equals(name)) {
                        if (realValue == null) {
                            realValue = Constant.GLOBAL_EMPTY;
                        }
                        testDataMap.put(name, realValue);
                        stationId = realValue;
                        if (stationId == null) {
                            stationId = "";
                        }
                    } else if (TestData.PSA_SLOT_NO.equals(name)) {
                        if (realValue == null) {
                            realValue = Constant.GLOBAL_EMPTY;
                        }
                        testDataMap.put(name, realValue);
                        slotNo = realValue;
                        if (slotNo == null) {
                            slotNo = "";
                        }
                    } else if (TestData.PSA_START_TIME.equals(name) || TestData.PSA_STOP_TIME.equals(name)) {
                        if (string2Date(realValue) == null) {
                            errorMessage.get("failTotal").add(rows);
                            return testData;
                        } else {
                            testDataMap.put(name, string2Date(realValue));
                        }
                    } else if (TestData.PSA_IS_PASS.equals(name)) {
                        testDataMap.put(name, realValue == null || !realValue.equalsIgnoreCase("PASS") ? false : true);
                    } else if (TestData.PSA_FAIL_TEST_ITEMS.equals(name)) {
                        if (realValue != null) {
                            String[] failItems = realValue.split(";");
                            Collections.addAll(failItemSet, failItems);
                            testDataMap.put(name, failItemSet);
                        }
                    } else {
                        testDataMap.put(name, realValue);
                    }
                } else {
                    DUTAttribute dutAttribute = new DUTAttribute();
                    dutAttribute.setAttributeName(name);
                    dutAttribute.setAttributeValue(realValue);
                    dutAttributes.add(dutAttribute);
                }
            }
        }
        if (stationMap.containsKey(stationId)) {
            if ((!StringUtils.isSpecialBlank(slotNo) || slotNo == "") && !stationMap.get(stationId).contains(slotNo)) {
                stationMap.get(stationId).add(slotNo);
            }
        } else {
            List<String> slotNoList = Lists.newArrayList();
            if (!StringUtils.isSpecialBlank(slotNo) || slotNo == "") {
                slotNoList.add(slotNo);
            }
            stationMap.put(stationId, slotNoList);
            slotNoList = null;
        }
        List<TestItem> testItemList = Lists.newArrayList();
        for (int i = 1; i < row.size(); i++) {
            Object object = columnTable.row(0L).get(i);
            if (object == null) {
                continue;
            }

            String columnName = object.toString();
            if (psAttributes.contains(columnName)) {
                continue;
            }

            String itemName = object == null ? null : object.toString();
            String itemNameStr = StringUtils.isSpecialBlank(itemName) ? null : itemName;
            if (itemNameStr == null) {
                continue;
            }
            TestItem testItem = new TestItem();
            testItem.setName(itemNameStr);
            testItem.setIsPass(true);
            String itemValue = null;
            BigDecimal bd = null;
            if (StringUtils.isNotBlank(row.get(itemNameStr))) {
                itemValue = row.get(itemNameStr).toString();
                try {
                    bd = new BigDecimal(itemValue);
                    itemValue = bd.toPlainString();
                } catch (Exception e) {
                    Pattern pattern = Pattern.compile("^([0-9]{1,3}(,[0-9]{3})*(\\.[0-9]+)?|\\.[0-9]+)$");
                    if (StringUtils.isNotBlank(itemValue) && pattern.matcher(itemValue).matches()) {
                        DecimalFormat df = new DecimalFormat();
                        try {
                            itemValue = df.parse(itemValue).toString();
                            bd = new BigDecimal(itemValue);
                            itemValue = bd.toPlainString();
                        } catch (ParseException e1) {
                        }
                    }
                }
            }
            testItem.setTestedValue(itemValue);

            if (StringUtils.isSpecialBlank(itemValue) || failItemSet.contains(itemNameStr)) {
                testItem.setIsPass(false);
            }
            String ul = columnTable.row(1L).get(i) == null ? null : columnTable.row(1L).get(i).toString();
            String ulStr = StringUtils.isSpecialBlank(ul) ? null : ul;
            testItem.setUpperLimit(ulStr);

            String lol = columnTable.row(2L).get(i) == null ? null : columnTable.row(2L).get(i).toString();
            String lolStr = StringUtils.isSpecialBlank(lol) ? null : lol;
            testItem.setLowerLimit(lolStr);

            String unit = columnTable.row(3L).get(i) == null ? null : columnTable.row(3L).get(i).toString();
            String unitName = StringUtils.isSpecialBlank(unit) ? null : unit;
            testItem.setUnitName(unitName);

            testItem.setIsNumeric(false);
            if (StringUtils.isNumeric(itemValue)) {
                testItem.setIsNumeric(true);
            }
            testItemList.add(testItem);
            if (itemNameMap.get(itemNameStr) != null && itemNameMap.get(itemNameStr)) {
                continue;
            }
            itemNameMap.put(itemNameStr, testItem.getIsNumeric());
        }

        testData.setTestItems(testItemList);

        testData.setProjectId(project.getId().toString());
        testData.setGrrName(grrName);
        testData.setTestData(testDataMap);
        testData.setDelete(false);
        if (dutAttributes != null && !dutAttributes.isEmpty()) {
            testData.setDutAttributes(dutAttributes);
        }
        return testData;
    }

    private Date string2Date(String d) {
        Date date = null;
        try {
            if (StringUtils.isBlank(d)) {
                date = null;
            } else {
                if (d.split(":").length == 1) {
                    date = null;
                } else {
                    if (d.split(":").length == 2 && d.endsWith(":")) {
                        d = d + "00";
                    } else if (d.split(":").length == 2 && !d.endsWith(":")) {
                        d = d + ":00";
                    }
                }

                date = DateTime.parse(d, DateTimeFormat.forPattern(systemConfig.getDateFormat())).toDate();
            }
        } catch (Exception e) {
            SimpleDateFormat sdf = new SimpleDateFormat(systemConfig.getDateFormat());
            sdf.setLenient(false);

            SimpleDateFormat sdf1 = new SimpleDateFormat("EEE MMM dd HH:mm:ss Z yyyy", Locale.US);

            try {
                date = DateTime.parse(sdf.format(sdf1.parse(d)), DateTimeFormat.forPattern(systemConfig.getDateFormat())).toDate();
            } catch (ParseException ex) {
                SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                sdf2.setLenient(false);

                try {
                    date = DateTime.parse(sdf.format(sdf2.parse(d)), DateTimeFormat.forPattern(systemConfig.getDateFormat())).toDate();
                } catch (ParseException exx) {
                    SimpleDateFormat sdf3 = new SimpleDateFormat("yyyy/MM/dd/HH:mm:ss");
                    sdf3.setLenient(false);

                    try {
                        date = DateTime.parse(sdf.format(sdf3.parse(d)), DateTimeFormat.forPattern(systemConfig.getDateFormat())).toDate();
                    } catch (ParseException exxx) {
                        return null;
                    }
                }
            }
        }

        return date;
    }

    private String getCellVaule(Cell cell) {
        String cellValue = null;
        if (cell == null) {
            return cellValue;
        }

        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_STRING:
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = cell.getDateCellValue().toString();
                } else {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_ERROR:
                cellValue = null;
                break;
            case Cell.CELL_TYPE_FORMULA:
                cellValue = null;
                break;
            default:
                cellValue = null;
        }
        return cellValue;
    }

    private void clearSpcAnalyseCache(String projectId) {
        if (projectId == null) {
            throw new ApplicationException(ExceptionMessages.EXCEPTION_PARAMETER_INPUT_INVALID);
        }
        logger.debug("Checking project: {}", projectId);

        List<String> testDataList = Lists.newArrayList();
        Set<String> keys = testDataCacheFactory.getCacheTestDatas().asMap().keySet();
        for (String key : keys) {
            Map<String, Object> searchParams = mapper.fromJson(key, mapper.contructMapType(Map.class, String.class, Object.class));
            if (searchParams.get("projectId") != null) {
                String projectIdCache = searchParams.get("projectId").toString();
                if (projectIdCache.equals(projectId)) {
                    testDataList.add(key);
                }
            }
        }
        testDataCacheFactory.getCacheTestDatas().invalidateAll(testDataList);

        List<String> countList = Lists.newArrayList();
        Set<String> countkeys = testDataCacheFactory.getCacheTestDataCount().asMap().keySet();
        for (String key : countkeys) {
            Map<String, Object> searchParams = mapper.fromJson(key, mapper.contructMapType(Map.class, String.class, Object.class));
            if (searchParams.get("projectId") != null) {
                String projectIdCache = searchParams.get("projectId").toString();
                if (projectIdCache.equals(projectId)) {
                    countList.add(key);
                }
            }
        }
        testDataCacheFactory.getCacheTestDataCount().invalidateAll(countList);

        List<String> distinctTestDataList = Lists.newArrayList();
        Set<String> distinctKeys = testDataCacheFactory.getCacheDistinctTestDatas().asMap().keySet();
        for (String key : distinctKeys) {
            Map<String, Object> searchParams = mapper.fromJson(key, mapper.contructMapType(Map.class, String.class, Object.class));
            if (searchParams.get("projectId") != null) {
                String projectIdCache = searchParams.get("projectId").toString();
                if (projectIdCache.equals(projectId)) {
                    distinctTestDataList.add(key);
                }
            }
        }
        testDataCacheFactory.getCacheDistinctTestDatas().invalidateAll(distinctTestDataList);


        List<String> yieldList = Lists.newArrayList();
        Set<String> yieldKeys = reportCacheFactory.getCacheYield().asMap().keySet();
        for (String key : yieldKeys) {
            Map<String, Object> searchParams = mapper.fromJson(key, mapper.contructMapType(Map.class, String.class, Object.class));
            if (searchParams.get("projectId") != null) {
                String projectIdCache = searchParams.get("projectId").toString();
                if (projectIdCache.equals(projectId)) {
                    yieldList.add(key);
                }
            }
        }
        reportCacheFactory.getCacheYield().invalidateAll(yieldList);
    }

    private void createIndexForTestData(String projectId) {
        boolean isHasIndex = false;
        String collectionId = Constant.MONGODB_GRR_COLLECTION + projectId;
        IndexOperations io = mongoTemplate.indexOps(collectionId);
        List<IndexInfo> indexInfos = io.getIndexInfo();
        for (IndexInfo indexInfo : indexInfos) {
            if ("grrName".equals(indexInfo.getName())) {
                isHasIndex = true;
                break;
            }
        }

        if (!isHasIndex) {
            Index index1 = new Index();
            index1.named("grrName");
            index1.on("grrName", Sort.Direction.ASC);
            io.ensureIndex(index1);

            Index index2 = new Index();
            index2.named("startTime");
            index2.on("testData.startTime", Sort.Direction.ASC);
            io.ensureIndex(index2);

            Index index = new Index();
            index.named("searchKeys");
            index.on("grrName", Sort.Direction.ASC);
            index.on("testData.serialNumber", Sort.Direction.ASC);
            index.on("testData.slotNo", Sort.Direction.ASC);
            index.on("testData.startTime", Sort.Direction.ASC);
            io.ensureIndex(index);
        }
    }

    private void setStations(String projectId, Map<String, List<String>> stationMap) {
        if (stationMap != null) {
            Query query = new Query();
            Criteria criteria = new Criteria();
            criteria.and("projectId").is(projectId);
            query.addCriteria(criteria);
            List<TestStation> testStations = mongoTemplate.findAllAndRemove(query, TestStation.class);
            if (testStations == null || testStations.isEmpty()) {
                testStations = Lists.newArrayList();
                for (Map.Entry<String, List<String>> entry : stationMap.entrySet()) {
                    TestStation testStation = new TestStation();
                    testStation.setProjectId(projectId);
                    testStation.setStationId(entry.getKey());
                    testStation.setSlotNos(entry.getValue());
                    testStations.add(testStation);
                }
                mongoTemplate.insertAll(testStations);
                return;
            }
            List<String> stationIds = Lists.newArrayList();
            for (TestStation testStation : testStations) {
                stationIds.add(testStation.getStationId());
            }

            ListIterator lit = testStations.listIterator();
            for (Map.Entry<String, List<String>> entry : stationMap.entrySet()) {
                if (!stationIds.contains(entry.getKey())) {
                    TestStation testStation = new TestStation();
                    testStation.setProjectId(projectId);
                    testStation.setStationId(entry.getKey());
                    testStation.setSlotNos(entry.getValue());
                    lit.add(testStation);
                } else {
                    if (entry.getValue() == null || entry.getValue().isEmpty()) {
                        continue;
                    }
                    lit = testStations.listIterator();
                    while (lit.hasNext()) {
                        TestStation testStation = (TestStation) lit.next();
                        if ((testStation.getStationId() != null && testStation.getStationId().equals(entry.getKey())) || (testStation.getStationId() == "" && entry.getKey() == "")) {
                            for (String slotNo : entry.getValue()) {
                                if (!testStation.getSlotNos().contains(slotNo)) {
                                    testStation.getSlotNos().add(slotNo);
                                }
                            }
                        }
                    }
                }
            }
            mongoTemplate.insertAll(testStations);
        }
    }

    private void saveImportDataLog(Project project, String userLoginName, String filePath, Long fileSize, String result) {
        StringBuffer msg = new StringBuffer();
        msg.append(getLogCell(ApplicationModule.MODULE_DATA_IMPORT));
        msg.append(getLogCell("Upload File"));

        String fileName = filePath.substring(filePath.lastIndexOf(ApplicationUtil.getFileSeparator()) + 1);

        LogDto logDto = new LogDto();
        logDto.setCreateBy(userLoginName);
        logDto.setCreateTime(new Date());
        logDto.setOperation(LogUtil.INSERT.getCode());
        msg.append(getLogCell(",", "Project: " + project.getName(), "File: " + fileName, "FileSize: " + fileSize));
        logDto.setContent(msg.toString());
        logDto.setResult(result);
        logService.log(logDto);
    }

    private String getLogCell(String content) {
        return "[" + content + "]";
    }

    private String getLogCell(String sp, String... contents) {
        StringBuffer msg = new StringBuffer();
        msg.append("[");
        if (sp != null) {
            for (String s : contents) {
                msg.append(s).append(sp);
            }
        }
        msg.deleteCharAt(msg.lastIndexOf(sp));
        msg.append("]");
        return msg.toString();
    }
}
